package com.cheney.demo.data;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

//用Java，实现如图的转换，输入文件来自D:/tmp/import2.xlsx，输出文件保存在D:/tmp/export2.xlsx
public class KVKV差异_02 {
    public static void main(String[] args) throws IOException {
        // 读取输入Excel文件
        List<String[]> inputData = readExcelFile("D:/tmp/import2.xlsx");

        // 创建新的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Result");

        // 创建基础单元格样式
        CellStyle greenStyle = createCellStyle(workbook, IndexedColors.LIGHT_GREEN.getIndex());
        CellStyle yellowStyle = createCellStyle(workbook, IndexedColors.YELLOW.getIndex());
        CellStyle redStyle = createCellStyle(workbook, IndexedColors.RED.getIndex());

        // 将两个独立的样式Map合并为一个
        Map<String, CellStyle> keyStyles = new HashMap<>();
        short[] colors = {
            IndexedColors.LIGHT_BLUE.getIndex(),
            IndexedColors.PINK.getIndex(),
            IndexedColors.GREY_25_PERCENT.getIndex(),
            IndexedColors.LIGHT_TURQUOISE.getIndex(),
            IndexedColors.LIGHT_ORANGE.getIndex()
        };

        // 收集所有唯一的key值（合并a和b的key）
        Set<String> uniqueKeys = new HashSet<>();
        for (int i = 1; i < inputData.size(); i++) {
            String[] row = inputData.get(i);
            if (!row[0].isEmpty()) uniqueKeys.add(row[0]);
            if (!row[2].isEmpty()) uniqueKeys.add(row[2]);
        }

        // 为每个唯一的key创建对应的样式
        int colorIndex = 0;
        for (String key : uniqueKeys) {
            keyStyles.put(key, createCellStyle(workbook, colors[colorIndex % colors.length]));
            colorIndex++;
        }

        // 写入标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"a", "a1", "b", "b1", "KV完全相同", "存在K相同V不相同", "K不相同"};
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }

        // 收集所有的key-value对
        Map<String, Set<String>> aMap = new HashMap<>();
        Map<String, Set<String>> bMap = new HashMap<>();

        // 处理输入数据
        for (int i = 1; i < inputData.size(); i++) {
            String[] row = inputData.get(i);
            String keyA = row[0];
            String valueA = row[1];
            String keyB = row[2];
            String valueB = row[3];

            if (!keyA.isEmpty()) {
                aMap.computeIfAbsent(keyA, k -> new HashSet<>()).add(valueA);
            }
            if (!keyB.isEmpty()) {
                bMap.computeIfAbsent(keyB, k -> new HashSet<>()).add(valueB);
            }
        }

        int rowIndex = 1;

        // 处理相同key的情况
        for (String key : aMap.keySet()) {
            Set<String> aValues = aMap.get(key);
            Set<String> bValues = bMap.getOrDefault(key, new HashSet<>());

            if (bValues.isEmpty()) {
                // 只在A中存在的key（红色）
                for (String value : aValues) {
                    Row row = sheet.createRow(rowIndex++);
                    createCell(row, 0, key, redStyle);
                    createCell(row, 1, value, redStyle);
                    createCell(row, 2, "", null);
                    createCell(row, 3, "", null);
                    createCell(row, 4, "", null);
                    createCell(row, 5, "", null);
                    createCell(row, 6, "Y", null);  // K不相同
                }
            } else {
                // 当key相同时，使用lightBlueStyle
                for (String valueA : aValues) {
                    Row row = sheet.createRow(rowIndex++);
                    
                    if (bValues.contains(valueA)) {
                        // 值相同的情况
                        createCell(row, 0, key, keyStyles.get(key));
                        createCell(row, 1, valueA, greenStyle);
                        createCell(row, 2, key, keyStyles.get(key));
                        createCell(row, 3, valueA, greenStyle);
                        createCell(row, 4, "Y", null);
                        createCell(row, 5, "", null);
                        createCell(row, 6, "", null);
                    } else {
                        // 值不同的情况
                        createCell(row, 0, key, keyStyles.get(key));
                        createCell(row, 1, valueA, yellowStyle);
                        createCell(row, 2, key, keyStyles.get(key));
                        createCell(row, 3, "", yellowStyle);
                        createCell(row, 4, "", null);
                        createCell(row, 5, "Y", null);
                        createCell(row, 6, "", null);
                    }
                }
                
                // 处理在B中但不在A中的值
                for (String valueB : bValues) {
                    if (!aValues.contains(valueB)) {
                        Row row = sheet.createRow(rowIndex++);
                        createCell(row, 0, key, keyStyles.get(key));
                        createCell(row, 1, "", yellowStyle);
                        createCell(row, 2, key, keyStyles.get(key));
                        createCell(row, 3, valueB, yellowStyle);
                        createCell(row, 4, "", null);
                        createCell(row, 5, "Y", null);
                        createCell(row, 6, "", null);
                    }
                }
            }
        }

        // 处理只在B中存在的key（红色）
        for (String key : bMap.keySet()) {
            if (!aMap.containsKey(key)) {
                for (String valueB : bMap.get(key)) {
                    Row row = sheet.createRow(rowIndex++);
                    createCell(row, 0, "", null);
                    createCell(row, 1, "", null);
                    createCell(row, 2, key, redStyle);
                    createCell(row, 3, valueB, redStyle);
                    createCell(row, 4, "", null);
                    createCell(row, 5, "", null);
                    createCell(row, 6, "Y", null);  // K不相同
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 7; i++) {  // 改为7列
            sheet.autoSizeColumn(i);
        }

        // 保存文件
        try (FileOutputStream fileOut = new FileOutputStream("D:/tmp/export2.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
    }

    private static CellStyle createCellStyle(Workbook workbook, short color) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(color);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    private static void createCell(Row row, int column, String value, CellStyle style) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        if (style != null) {
            cell.setCellStyle(style);
        }
    }

    // 添加新的方法来读取Excel文件
    private static List<String[]> readExcelFile(String filePath) throws IOException {
        List<String[]> data = new ArrayList<>();
        try (FileInputStream inputStream = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                String[] rowData = new String[4];
                for (int i = 0; i < 4; i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (cell.getCellType() == CellType.BLANK) {
                        rowData[i] = "";
                    } else if (cell.getCellType() == CellType.NUMERIC) {
                        // 对于数字类型，转换为没有小数点的字符串
                        rowData[i] = String.format("%.0f", cell.getNumericCellValue());
                    } else {
                        rowData[i] = cell.toString().trim();
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }
}
